package com.rbmh.network.dao.Mapper;

import com.rbmh.network.bean.entity.DbInfo;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

@Mapper
public interface DbInfoMapper{

    // =============== 插入 ===============
    @Insert("INSERT INTO db_info(province, city, district, detail_address, ip_address, port, db_name, username, password, create_time, update_time) " +
            "VALUES(#{province}, #{city}, #{district}, #{detailAddress}, #{ipAddress}, #{port}, #{dbName}, #{username}, #{password}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(DbInfo dbInfo);

    // =============== 删除 ===============
    @Delete("DELETE FROM db_info WHERE id = #{id}")
    int deleteById(Long id);

    // =============== 更新 ===============
    @Update("UPDATE db_info SET " +
            "province = #{province}, " +
            "city = #{city}, " +
            "district = #{district}, " +
            "detail_address = #{detailAddress}, " +
            "ip_address = #{ipAddress}, " +
            "port = #{port}, " +
            "db_name = #{dbName}, " +
            "username = #{username}, " +
            "password = #{password}, " +
            "update_time = #{updateTime} " +
            "WHERE id = #{id}")
    int update(DbInfo dbInfo);

    // =============== 查询 ===============
    @Select("SELECT * FROM db_info WHERE id = #{id}")
    DbInfo selectById(Long id);

    @Select("SELECT * FROM db_info WHERE province = #{province}")
    DbInfo selectByProvince(String province);

    @Select("SELECT * FROM db_info WHERE city = #{city}")
    DbInfo selectByCity(String city);

    @Select("SELECT * FROM db_info WHERE district = #{district}")
    DbInfo selectByDistrict(String district);

    @Select("SELECT * FROM db_info WHERE detailAddress = #{detailAddress}")
    DbInfo selectByDetailAddress(String detailAddress);

    // 修改分页查询方法，支持更多查询条件
    @Select("SELECT * FROM db_info " +
            "WHERE (#{province} IS NULL OR province = #{province}) " +
            "AND (#{city} IS NULL OR city = #{city}) " +
            "AND (#{district} IS NULL OR district = #{district}) " +
            "AND (#{detailAddress} IS NULL OR detail_address = #{detailAddress}) " +
            "AND (#{ipAddress} IS NULL OR ip_address = #{ipAddress}) " +
            "ORDER BY ${orderBy} ${orderDir} " +
            "LIMIT #{offset}, #{pageSize}")
    @Results({
            @Result(column = "detail_address", property = "detailAddress"),
            @Result(column = "ip_address", property = "ipAddress"),
            @Result(column = "db_name", property = "dbName"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    List<DbInfo> selectByPage(
            @Param("province") String province,
            @Param("city") String city,
            @Param("district") String district,
            @Param("detailAddress") String detailAddress,
            @Param("ipAddress") String ipAddress,
            @Param("orderBy") String orderBy,
            @Param("orderDir") String orderDir,
            @Param("offset") int offset,
            @Param("pageSize") int pageSize
    );

    // 修改统计方法，支持更多查询条件
    @Select("SELECT COUNT(*) FROM db_info " +
            "WHERE (#{province} IS NULL OR province = #{province}) " +
            "AND (#{city} IS NULL OR city = #{city}) " +
            "AND (#{district} IS NULL OR district = #{district}) " +
            "AND (#{detailAddress} IS NULL OR detail_address = #{detailAddress}) " +
            "AND (#{ipAddress} IS NULL OR ip_address = #{ipAddress})")
    int countByConditions(
            @Param("province") String province,
            @Param("city") String city,
            @Param("district") String district,
            @Param("detailAddress") String detailAddress,
            @Param("ipAddress") String ipAddress);
}
